package io.renren.modules.training.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 封装poi对表格的数据解析工具类
 * @description 
 * @author ailong
 * @date: 2016年6月29日 下午3:44:21
 * @email ailong@able-elec.com
 * @version 
 * Copyright © 2003-2016 Zhihuishu. All rights reserved.
 */
public class PoiUtil {


	/**
	 * 将指定路径的excel文件封装成WorkBook对象
	  * @Description 
	  * @param xlsFile ecxel文件的全路径
	  * @author ailong
	  * @date 2016年6月29日 下午4:16:11
	 */
	public static Workbook getWorkbookByPath(String xlsFile){
		if(StringUtils.isEmpty(xlsFile)) return null;
		Workbook workbook = null;
		try {
			workbook =  WorkbookFactory.create(new FileInputStream(xlsFile));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return workbook;
	}
	/**
	 * 将指定的文件封装成WorkBook对象
	  * @Description 
	  * @param file ecxel文件的全路径
	  * @author ailong
	  * @date 2016年6月29日 下午4:29:03
	 */
	public static Workbook getWorkbookByFile(InputStream  inputStream){
		if(inputStream == null ) return null;
		Workbook workbook = null;
		try {
			workbook =  WorkbookFactory.create(inputStream);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return workbook;
	}
	
	/**
	 * 将workbook的所有sheet名和索引封装成map格式
	 * @param workbook
	 * @author ailong
	 * @date 2016年6月30日 上午9:19:17
	 * @return
	 */
	public static  HashMap<String, Integer> getSheets(Workbook workbook){
		if(workbook == null || workbook.getNumberOfSheets() == 0) return null;
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		int sheetNumber = workbook.getNumberOfSheets();
		for (int i = 0; i < sheetNumber; i++) {
			map.put(workbook.getSheetAt(i).getSheetName(),i);
		}
		return map;
	}
	
	/**
	 * 根据sheet名字获取sheet的内容
	  * @Description 
	  * @param workbook excel文本对象
	  * @param name sheet名字
	  * @param fromIndex excel文件内容的第一行号
	  * @author ailong
	  * @date 2016年6月30日 上午9:37:25
	 */
	public static List<TreeMap<Object, Object>> getExcelDataFormSheet(Workbook workbook, Integer templateType, String name, Integer fromIndex){
		List<TreeMap<Object, Object>> childMapList = new ArrayList<TreeMap<Object,Object>>();
		int index = getSheets(workbook).get(name);
		Sheet sheet = workbook.getSheetAt(index);
		int rows = sheet.getPhysicalNumberOfRows();
		for (int j = fromIndex.intValue(); j <rows; j++) {
			TreeMap<Object,Object> childMap = new TreeMap<Object,Object>();
			int cells = 0;
			if(sheet.getRow(j)!=null){
				cells = sheet.getRow(j).getPhysicalNumberOfCells();
			}
			for (int k = 0; k <cells; k++) {
				String modelField = getTemplateFiledByType(templateType).get(getFirstRowCellsOfSheet(workbook, name).get(k+""));
				Cell cell = sheet.getRow(j).getCell(k);
				if(cell!=null){
					int type = cell.getCellType();
					switch(type){
						case Cell.CELL_TYPE_STRING :
							String result = "";
							if(StringUtils.isNotEmpty(cell.getStringCellValue().trim())){
								result = cell.getStringCellValue().trim();
							}
							childMap.put(modelField,result);
							break;
						case Cell.CELL_TYPE_NUMERIC:
							if(DateUtil.isCellDateFormatted(cell)){
								childMap.put(modelField,cell.getDateCellValue());break;
							}else{
								childMap.put(modelField,(int)cell.getNumericCellValue());break;
							}
						case Cell.CELL_TYPE_BOOLEAN :childMap.put(modelField,cell.getBooleanCellValue());break;
						case Cell.CELL_TYPE_FORMULA :childMap.put(modelField,cell.getCellFormula());break;
						default:break;
					}
				}else{
					childMap.put(modelField,"");
				}
			}
			childMapList.add(childMap);
		}
		return childMapList;
	}

	/**
	 * 获取excel文件的第一行的所有列名
	  * @Description 
	  * @param workbook excel文本对象
	  * @param name sheet名字
	  * @author ailong
	  * @date @date 2016年6月30日 上午10:39:05
	 */
	public static TreeMap<Integer, String> getFirstRowCellsOfSheet(Workbook workbook, String name){
		if(workbook == null || StringUtils.isEmpty(name)) return null;
		Integer index = getSheets(workbook).get(name);
		if(index == null) return null;
		Sheet sheet = workbook.getSheetAt(index);
		int childCellNum = sheet.getRow(1).getPhysicalNumberOfCells();
		TreeMap<Integer, String> hashMap = new TreeMap<Integer, String>();
		//第一行主标题列
		for(int i = 0; i < childCellNum; i++){
			Cell cell = sheet.getRow(0).getCell(i);
			String thead = cell.getStringCellValue().trim();
			if(StringUtils.isEmpty(thead)) break;
			hashMap.put(i, thead);
		}
		//第二行副标题列
		for(int k = 8; k < childCellNum; k++) {
			Cell cell = sheet.getRow(1).getCell(k);
			String thead = cell.getStringCellValue().trim();
			if(StringUtils.isEmpty(thead)) break;
			hashMap.put(k+1, thead);
		}
		return hashMap;
	}

	/**
	 * 获取表格第一行标题
	 * @param workbook
	 * @param name
	 * @return
	 */
	public static TreeMap<Integer, String> getFirstRowCells(Workbook workbook, String name){
		if(workbook == null || StringUtils.isEmpty(name)) return null;
		Integer index = getSheets(workbook).get(name);
		if(index == null) return null;
		Sheet sheet = workbook.getSheetAt(index);
		int childCellNum = sheet.getRow(1).getPhysicalNumberOfCells();
		TreeMap<Integer, String> hashMap = new TreeMap<Integer, String>();
		//第一行主标题列
		for(int i = 0; i < childCellNum; i++){
			Cell cell = sheet.getRow(1).getCell(i);
			String thead = cell.getStringCellValue().trim();
			if(StringUtils.isEmpty(thead)) break;
			hashMap.put(i, thead);
		}
		return hashMap;
	}
	
	/**
	 * 根据上传功能的名称获取模版列属性
	  * @Description 
	  * @author ailong
	  * @date: 2016年6月30日 上午11:42:45
	  * @param type 上传功能的名称
	  * @return
	 */
	public static HashMap<Integer, String> getTemplateFiledByType(Integer type){
		if(type == null) return null;
		HashMap<Integer, String> map = new HashMap<Integer, String>();
		switch (type) {
		case 1:
			map.put(0, "序号");
			map.put(1, "题目类型");
			map.put(2, "题干");
			map.put(3, "答案");
			map.put(4, "分数");
			map.put(5, "难度");
			map.put(6, "解析");
			map.put(7, "评分标准");
			map.put(8, "选项/填空题答案【填空题答案填在下面，一个答案对应一个单元格】");
			map.put(9, "A");
			map.put(10, "B");
			map.put(11, "C");
			map.put(12, "D");
			map.put(13, "E");
			map.put(14, "F");
			map.put(15, "G");
			map.put(16, "H");
			break;
		default:
			break;
		}
		return map;
	}
	
	

	public static Map<String,String> getAllQuestionValidField(){
		Map<String, String> map = new HashMap<String, String>();
		map.put("单选题", "题目类型,题干,选项,分数,答案");
		map.put("多选题", "题目类型,题干,选项,分数,答案");
		map.put("填空题", "题目类型,题干,答案,分数");
		map.put("判断题", "题目类型,题干,分数,答案");
		map.put("问答题", "题目类型,题干,分数");
		return map;
	}


	private static String getAnswer(String answerOption,DecimalFormat format,Map<Integer, String> nameKeyMap,Row row){
		String answerContent = null;
		if(!nameKeyMap.containsValue(answerOption)) return answerContent;
		for(Integer key : nameKeyMap.keySet()){
			if(answerOption.equals(nameKeyMap.get(key))){
				Cell answerCell = row.getCell(key-1);
				if(answerCell != null){
					switch (answerCell.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						answerContent = answerCell.getStringCellValue().trim();
						break;
					case Cell.CELL_TYPE_NUMERIC:
						answerContent = DateAndNumToString((XSSFCell)answerCell);
						break;
					default:
						answerContent = answerCell.getStringCellValue().trim();
						break;
					}
				}
			}
		}
		return answerContent;
	}
	

    /**
     * 根据XSSFCell类型设置数据
     * @param cell
     * @return
     */
    public static String getCellValue(XSSFCell cell) {
        if (cell == null) {
            return null;
        }
        String ret;
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_BLANK:
                ret = "";
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                ret = null;
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                XSSFWorkbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                ret = getCellValue((XSSFCell) evaluator.evaluateInCell(cell));
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
//                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
//                    Date theDate = cell.getDateCellValue();
//                    SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
//                    ret = sdf.format(theDate);
//                } else {
                 //   ret = NumberToTextConverter.toText(cell.getNumericCellValue());
//                }
            	ret = DateAndNumToString(cell);            	
                break;
            case XSSFCell.CELL_TYPE_STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = null;
        }
        return ret;
    }
    
    private static String DateAndNumToString(XSSFCell cell) {
    	String ret = null;
    	short format = cell.getCellStyle().getDataFormat();  
    	if (HSSFDateUtil.isCellDateFormatted(cell) || format == 14 || format == 31
				|| format == 57 || format == 58 || format == 17 || format == 177 || format == 176 ||  (format >= 185 && format <= 189) || (format >= 198 && format <= 212) ) {
             SimpleDateFormat sdf = null;  
             if(format == 14 || format == 31 || format == 57 || format == 58 || format == 17 ||  (format >= 176 && format <= 180)
					 || (format >= 185 && format <= 189) || (format >= 192 && format <= 196) || (format >= 198 && format <= 212)){
                 //日期  
                 sdf = new SimpleDateFormat("yyyy/M/d");  
             }else if (format == 20 || format == 32 || format == 21) {  
                 //时间  
                 sdf = new SimpleDateFormat("H:mm:ss");  
             }else{
            	 sdf = new SimpleDateFormat("yyyy/M/d H:mm:ss");  
             }
             double value = cell.getNumericCellValue(); 
             Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
             ret = sdf.format(date);

			//System.out.println("ret "+ret +"format "+format);
        } else if(format == 9 || format == 10 ){
        	 ret = NumberToTextConverter.toText(cell.getNumericCellValue()*100)+"%";
        }else { // 纯数字   
        	 ret = NumberToTextConverter.toText(cell.getNumericCellValue());
			// System.out.println("ret "+ret +"format "+format);
        }            	
		return ret;
	}
    
    public static void main(String[] args) {
        System.out.println(HSSFDataFormat.getBuiltinFormat("0.00%"));  //10
        System.out.println(HSSFDataFormat.getBuiltinFormat("0%"));  // 9
        System.out.println(HSSFDataFormat.getBuiltinFormat("0.000%")); // -1
        System.out.println(HSSFDataFormat.getBuiltinFormat("0.0%"));
        System.out.println(HSSFDataFormat.getBuiltinFormat("56556"));

    }
    
    

    public static RichCellInfo getRichCellValue(Cell cell) {
        try {
            return getRichCellValue((XSSFCell) cell);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }




    /**
     * 表示一个单元格连续几个字符都具有相同格式
     * @param rCell
     * @return
     */
    public static List<ContinuousFont> getContinuousFonts(XSSFRichTextString rCell) {
        String rawValue=rCell.getString();

        Integer valLength = rCell.length();
        List<ContinuousFont> split = new ArrayList<ContinuousFont>();
        ContinuousFont prev=null;
        for (int i = 0; i < valLength; i++) {
            XSSFFont ft = rCell.getFontAtIndex(i);
            char charAt = rawValue.charAt(i);

            if(prev!=null){
                ContinuousFont nRs = prev.append(charAt, ft);
                if(nRs!=prev){
                    split.add(nRs);
                    prev=nRs;
                }
            }

            if(prev == null){
                prev = new ContinuousFont(charAt,ft);
                split.add(prev);
            }
        }
        return split;
    }

    public static class ContinuousFont{
        XSSFFont font;
        String str;

        public ContinuousFont(char str,XSSFFont font) {
            this.str = new String(new char[]{str});
            this.font = font;
        }

        ContinuousFont append(char str, XSSFFont font){
            if(font.equals(this.font)){
                this.str+=str;
                return this;
            }else {
                return new ContinuousFont(str,font);
            }
        }

        @Override
        public String toString() {
            return "ContinuousFont{" +
                    "font=" + font +
                    ", str='" + str + '\'' +
                    '}';
        }
    }

    public static class RichCellInfo{
        private int code=0;//0:无格式纯文本 1:整体带格式的文本 2:部分带格式的文本
        private boolean isRichText=false;// code!=0  -> true
        private boolean hasLineEnd=false;// 是否拥有换行符
        private String rawValue;//原生值 纯文本值
        private String htmlValue;//转换成的带格式的单元格内容

        public boolean isRichText() {
            return isRichText;
        }

        public void setRichText(boolean richText) {
            isRichText = richText;
        }

        public int getCode() {
            return code;
        }

        public void setCode(int code) {
            this.code = code;
        }

        public String getRawValue() {
            return rawValue;
        }

        public void setRawValue(String rawValue) {
            this.rawValue = rawValue;
        }

        public String getHtmlValue() {
            return htmlValue;
        }

        public void setHtmlValue(String htmlValue) {
            this.htmlValue = htmlValue;
        }

        public boolean isHasLineEnd() {
            return hasLineEnd;
        }

        public void setHasLineEnd(boolean hasLineEnd) {
            this.hasLineEnd = hasLineEnd;
        }

        public RichCellInfo() {
        }

        public RichCellInfo(String rawValue) {
            this.rawValue = rawValue;
            this.htmlValue=rawValue;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj==null) {
                return false;
            }
            if (!(obj instanceof RichCellInfo )){
                return false;
            }

            return this.rawValue.equals((RichCellInfo)obj);
        }

        @Override
        public String toString() {
            return "RichCellInfo{" +
                    "code=" + code +
                    ", isRichText=" + isRichText +
                    ", hasLineEnd=" + hasLineEnd +
                    ", rawValue='" + rawValue + '\'' +
                    ", htmlValue='" + htmlValue + '\'' +
                    '}';
        }
    }

    public static boolean isNullRow(Row row, Integer validateCellNum){
		if(row == null || validateCellNum == null || validateCellNum <=0) return true;
		boolean isNullRow = true;
		for(int i=0;i<validateCellNum;i++){
			Cell cell = row.getCell(i);
			if(cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
				isNullRow = false;
				break;
			}
		}
		return isNullRow;
	}
}
